#include "managementdatabaseoperations.h"
#include <QDebug>
#include "common.h"
#include "tabledefine.h"

QSqlDatabase ManagementDB;
QStringList oneProjectInfo;

bool connectManagementDB(QString dbName)
{
    if (QSqlDatabase::contains(MANAGEMENTDB_CONNECTION))
    {
        ManagementDB = QSqlDatabase::database(MANAGEMENTDB_CONNECTION);
    }
    else
    {
        ManagementDB = QSqlDatabase::addDatabase("QSQLITE", MANAGEMENTDB_CONNECTION);
        ManagementDB.setDatabaseName(dbName);
        ManagementDB.setUserName("yanshang");
        ManagementDB.setPassword("912");
    }
    if(!ManagementDB.open())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<ManagementDB.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"连接并打开管理数据库成功";
    return true;
}

bool initManagementDB()
{
    QSqlQuery query(ManagementDB);
    QStringList tables = ManagementDB.tables();
    
     //创建项目管理表
    QString sql = QString("create table %1 (%2 INTEGER primary key autoincrement, "
                  "%3 VARCHAR(200), "
                  "%4 DateTime,"
                  "%5 DateTime,"
                  "%6 int)").arg(PM_TABLE_NAME, PROJECT_ID, 
                                 PROJECT_NAME, 
                                 PROJECT_CREATE_DATE_TIME, 
                                 PROJECT_LAST_CHANGE_DATE_TIME, 
                                 FLAG);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(PM_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<ManagementDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"项目管理表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"项目管理表已存在";
    
    //创建设备管理表（即产品）
    sql = QString("create table %1 (%2 INTEGER primary key, "
                  "%3 VARCHAR(200),"
                  "%4 VARCHAR(200),"
                  "%5 VARCHAR(200),"
                  "%6 VARCHAR(100),"
                  "%7 VARCHAR(200),"
                  "%8 VARCHAR(20),"
                  "%9 VARCHAR(200))")
            .arg(DM_TABLE_NAME).arg(PRODUCT_ID)
            .arg(NAME)
            .arg(MANUFACTURERS)
            .arg(ORDER_NUM)
            .arg(MEDIUM_TYPE)
            .arg(APP_PROGRAM)
            .arg(VERSION)
            .arg(DEVICEDB_NAME);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!tables.contains(DM_TABLE_NAME, Qt::CaseInsensitive))
    {
        if(!query.exec(sql))
        {
            qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"create error"<<ManagementDB.lastError();
            return false;
        }
        else
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备管理表创建完成";
    }
    else
        qWarning()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"设备管理表已存在";
    
    
    sql = QString("update %1 set %2 = %3").arg(PM_TABLE_NAME, FLAG, _FLAG);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    query.prepare(sql);
    query.bindValue(_FLAG, DEFAULT_FLAG);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"初始化flag失败"<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"初始化flag成功";
    return true;
}

bool insertProject(int projectID, QString projectName, 
                                      QString projectCreateDateTime, QString projectLastChangeDateTime)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select %1 from %2").arg(PROJECT_NAME, PM_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    while(query.next())
    {
        if(query.value(0).toString() == projectName)
        {
            qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"项目名称已存在"<<query.value(0).toString();
            QString tmp = QString("(%1)").arg(projectID - 1);
            projectName.append(tmp);
        }
    }
    
    sql = QString("insert into %1 values (?, ?, ?, ?, ?)").arg(PM_TABLE_NAME);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(projectID);
    query.addBindValue(projectName);
    query.addBindValue(projectCreateDateTime);
    query.addBindValue(projectLastChangeDateTime);
    query.addBindValue(DEFAULT_FLAG);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    return true;
}

bool insertProduct(int productID, QString name, QString manufacturers, QString orderNum, 
               QString mediumType, QString appProgram, QString version, QString deviceDBName)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("insert into %1 values (?, ?, ?, ?, ?, ?, ?, ?)").arg(DM_TABLE_NAME);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    query.addBindValue(productID);
    query.addBindValue(name);
    query.addBindValue(manufacturers);
    query.addBindValue(orderNum);
    query.addBindValue(mediumType);
    query.addBindValue(appProgram);
    query.addBindValue(version);
    query.addBindValue(deviceDBName);
    if(!query.exec())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    return true;
}

bool deleteProduct(int productID)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(DM_TABLE_NAME, PRODUCT_ID);
    
    if(!query.prepare(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    query.addBindValue(productID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool deleteProject(int projectID)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("delete from %1 where %2 = ?").arg(PM_TABLE_NAME, PROJECT_ID);
    
    query.prepare(sql);
    query.addBindValue(projectID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"删除成功";
    return true;
}

bool updateProjectName(int projectID, QString projectName)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     PROJECT_NAME, _PROJECT_NAME, 
                                                                     PROJECT_ID, _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_NAME, projectName);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新项目名称成功";
    return true;
}

bool updateProjectLastChangeDateTime(int projectID, QString lastChangeDateTime)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     PROJECT_LAST_CHANGE_DATE_TIME, 
                                                                     _PROJECT_LAST_CHANGE_DATE_TIME
                                                                     PROJECT_ID, _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_LAST_CHANGE_DATE_TIME, lastChangeDateTime);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新项目最终修改时间成功";
    return true;
}

bool updateProjectFlagByID(int projectID, int flagValue)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("update %1 set %2 = %3").arg(PM_TABLE_NAME, FLAG, _FLAG);
    
    query.prepare(sql);
    query.bindValue(_FLAG, DEFAULT_FLAG);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    
    sql = QString("update %1 set %2 = %3 where %4 = %5").arg(PM_TABLE_NAME, 
                                                                     FLAG, _FLAG, 
                                                                     PROJECT_ID, _PROJECT_ID);
    query.prepare(sql);
    query.bindValue(_FLAG, flagValue);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"更新flag成功";
    return true;
}

int selectNextProjectID()
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select max(%1) from %2").arg(PROJECT_ID, PM_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取数据库项目最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

int selectNextProductID()
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select max(%1) from %2").arg(PRODUCT_ID, DM_TABLE_NAME);
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取产品最大id成功";
        return (query.value(0).toInt() + 1);
    }
    return -2;
}

bool selectOneProjectInfo(int projectID)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select %1,%2,%3 from %4 where %5 = %6").arg(PROJECT_NAME, 
                                                                       PROJECT_CREATE_DATE_TIME, 
                                                                       PROJECT_LAST_CHANGE_DATE_TIME, 
                                                                       PM_TABLE_NAME, 
                                                                       PROJECT_ID, 
                                                                       _PROJECT_ID);
    
    query.prepare(sql);
    query.bindValue(_PROJECT_ID, projectID);
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return false;
    }
    if(query.first())
    {
        oneProjectInfo.clear();
        oneProjectInfo.append(query.value(0).toString());
        oneProjectInfo.append(query.value(1).toString());
        oneProjectInfo.append(query.value(2).toString());
        return true;
    }
    return false;
}

int selectProjectIDByFlag(int falgValue)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select %1 from %2 where %3 = %4").arg(PROJECT_ID, PM_TABLE_NAME,
                                                                 FLAG, _FLAG);
    
    query.prepare(sql);
    query.bindValue(_FLAG, falgValue);
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!query.exec())
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    if(query.first())
    {
        qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<"获取到的项目id为:"<<query.value(0).toInt();
        return query.value(0).toInt();
    }
    return -2;
}

int isProductNameExistsInDM(QString productName)
{
    QSqlQuery query(ManagementDB);
    QString sql = QString("select %1 from %2").arg(NAME, DM_TABLE_NAME);
    
    qDebug()<<"file:"<<__FILE__<<"line:"<<__LINE__<<sql;
    if(!query.exec(sql))
    {
        qCritical()<<"file:"<<__FILE__<<"line:"<<__LINE__<<query.lastError();
        return -1;
    }
    while(query.next())
    {
        if(query.value(0).toString() == productName)
            return 0;
    }
    return 1;
}
